﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using NLite.Data.SqlTypes;
using NLite.Data.Dialect.Function;
using NLite.Collections;
using NLite.Reflection;
using System.Reflection;
namespace NLite.Data.Dialect
{
 

    public class SQLiteDialect:Dialect
    {
        protected override void RegisterColumnTypes()
        {
            RegisterColumnType(DbTypeCode.Binary, "BLOB");
            RegisterColumnType(DbTypeCode.Blob, "BLOB");
            RegisterColumnType(DbTypeCode.Clob, "TEXT");
            RegisterColumnType(DbTypeCode.NClob, "TEXT");
            RegisterColumnType(DbTypeCode.Byte, "INTEGER");
            RegisterColumnType(DbTypeCode.Int16, "INTEGER");
            RegisterColumnType(DbTypeCode.Int32, "INTEGER");
            RegisterColumnType(DbTypeCode.Int64, "INTEGER");
            RegisterColumnType(DbTypeCode.SByte, "INTEGER");
            RegisterColumnType(DbTypeCode.UInt16, "INTEGER");
            RegisterColumnType(DbTypeCode.UInt32, "INTEGER");
            RegisterColumnType(DbTypeCode.UInt64, "INTEGER");
            RegisterColumnType(DbTypeCode.Currency, "NUMERIC");
            RegisterColumnType(DbTypeCode.Decimal, "NUMERIC");
            RegisterColumnType(DbTypeCode.Double, "NUMERIC");
            RegisterColumnType(DbTypeCode.Single, "NUMERIC");
            RegisterColumnType(DbTypeCode.VarNumeric, "NUMERIC");
            RegisterColumnType(DbTypeCode.AnsiString, "TEXT");
            RegisterColumnType(DbTypeCode.String, "TEXT");
            RegisterColumnType(DbTypeCode.AnsiStringFixedLength, "TEXT");
            RegisterColumnType(DbTypeCode.StringFixedLength, "TEXT");

            RegisterColumnType(DbTypeCode.Date, "DATETIME");
            RegisterColumnType(DbTypeCode.DateTime, "DATETIME");
            RegisterColumnType(DbTypeCode.Time, "DATETIME");
            RegisterColumnType(DbTypeCode.Boolean, "INTEGER");
            RegisterColumnType(DbTypeCode.Guid, "UNIQUEIDENTIFIER");
        }

        static readonly IDictionary<string, IFunctionView> customerFunctions;
        const string SQLiteFunctionTypeString = "System.Data.SQLite.SQLiteFunction,System.Data.SQLite";
        const string SQLiteFunctionAttributeTypeString = "System.Data.SQLite.SQLiteFunctionAttribute,System.Data.SQLite";

        public const string CustomFunctionAssemblyDescriptionFlag = "SQLite custom functions";

        static SQLiteDialect()
        {
            customerFunctions = new Dictionary<string, IFunctionView>(StringComparer.InvariantCultureIgnoreCase);

            var sqliteFunctionType = ServiceLocator.Get<IClassLoader>().LoadType(SQLiteFunctionTypeString);
            var sqliteFunctionAttributeType = ServiceLocator.Get<IClassLoader>().LoadType(SQLiteFunctionAttributeTypeString);

            if(sqliteFunctionType == null)
                return;
            if (sqliteFunctionAttributeType == null)
                return;

            var asms =(from asm in  AppDomain.CurrentDomain.GetAssemblies()
                       let attr = asm.GetAttribute<AssemblyDescriptionAttribute>(false)
                       where attr != null && attr.Description.Contains(CustomFunctionAssemblyDescriptionFlag)
                       select asm).ToArray();

            var fnNames = (
                from asm in asms
                from type in asm.GetTypes()
                let attr = type.GetAttribute(sqliteFunctionAttributeType,false)
                where  sqliteFunctionType.IsAssignableFrom(type)
                    && attr != null
                    && attr.GetProperty("FuncType").ToString() == "Scalar"
                    && !string.IsNullOrEmpty(attr.GetProperty<string>("Name"))
                    && !type.IsAbstract
                select attr.GetProperty<string>("Name")).ToArray();

            foreach (var item in fnNames)
            {
                if (!customerFunctions.ContainsKey(item))
                    customerFunctions.Add(item, new StandardFunctionView(item));
            }

        }

        public SQLiteDialect()
        {
            foreach (var item in customerFunctions)
                RegisterFunction(item.Key, item.Value);
        }

        protected override void RegisterDateFunction()
        {
            RegisterFunction(FunctionType.DateTime.Current, new ProxyFunctionView((v,a)=>v.Builder.Append("datetime('now')")));
            RegisterFunction(FunctionType.DateTime.New, new FunctionTemplateView("CAST(?1 as DateTime)"));

            RegisterFunction(FunctionType.DateTime.GetDayOfWeek, new FunctionTemplateView("strftime(\"%w\", ?1)"));
            RegisterFunction(FunctionType.DateTime.GetDayOfYear, new FunctionTemplateView("(strftime(\"%j\", ?1)-1)"));

            RegisterFunction(FunctionType.DateTime.GetMillisecond, new FunctionTemplateView("strftime(\"%f\", ?1)"));
            RegisterFunction(FunctionType.DateTime.GetSecond, new FunctionTemplateView("strftime(\"%S\", ?1)"));
            RegisterFunction(FunctionType.DateTime.GetMinute, new FunctionTemplateView("strftime(\"%M\", ?1)"));
            RegisterFunction(FunctionType.DateTime.GetHour, new FunctionTemplateView("strftime(\"%H\", ?1)"));
            RegisterFunction(FunctionType.DateTime.GetDay, new FunctionTemplateView("strftime(\"%d\", ?1)"));
            RegisterFunction(FunctionType.DateTime.GetMonth, new FunctionTemplateView("strftime(\"%m\", ?1)"));
            RegisterFunction(FunctionType.DateTime.GetYear, new FunctionTemplateView("strftime(\"%Y\", ?1)"));
          
        }

        class RemoveFunctionView : IFunctionView
        {
            private static readonly IFunctionView LocateWith2Params = new FunctionTemplateView("REPLACE(?1, SUBSTR(?1, ?2), '')");
            private static readonly IFunctionView LocateWith3Params = new FunctionTemplateView("REPLACE(?1, SUBSTR(?1, ?2, ?3), '')");

            public void Render(NodeWriter visitor, params INode[] args)
            {
                if (args == null)
                    throw new ArgumentNullException("args");
                if (args.Length != 2 && args.Length != 3)
                    throw new ArgumentException("'remove' function takes 2 or 3 arguments");
                if (args.Length == 2)
                    LocateWith2Params.Render(visitor, args);
                else
                    LocateWith3Params.Render(visitor, args);
            }
        }

        protected override void RegisterStringFunction()
        {
            RegisterFunction(FunctionType.String.Concat, new VarArgsFunctionView("(", "||", ")"));
            RegisterFunction(FunctionType.String.Upper, new StandardFunctionView("upper"));
            RegisterFunction(FunctionType.String.Lower, new StandardFunctionView("lower"));
            RegisterFunction(FunctionType.String.Length, new StandardFunctionView("LENGTH"));
            RegisterFunction(FunctionType.String.Replace, new StandardSafeFunctionView("replace", 3));
            RegisterFunction(FunctionType.String.SubString, new StandardFunctionView("substr"));
            RegisterFunction(FunctionType.String.Trim, new StandardFunctionView("trim"));
            RegisterFunction(FunctionType.String.LTrim, new StandardFunctionView("ltrim"));
            RegisterFunction(FunctionType.String.RTrim, new StandardFunctionView("rtrim"));

            RegisterFunction(FunctionType.String.Left, new FunctionTemplateView("substr(?1, 1, ?2)"));
            RegisterFunction(FunctionType.String.Right, new FunctionTemplateView("substr(?1, -?2)"));
            RegisterFunction(FunctionType.String.Remove, new RemoveFunctionView());

            //RegisterFunction(FunctionType.ToChar, new StandardFunctionView("char"));
            //RegisterFunction(FunctionType.ToNChar, new StandardFunctionView("nchar"));

            RegisterFunction(FunctionType.Case, new CaseFunctionView());
            RegisterFunction(FunctionType.Cast, new CastFunctionView());
        }

        protected override void RegisterMathFunction()
        {
            RegisterFunction(FunctionType.Math.Abs, new StandardFunctionView("abs"));
            RegisterFunction(FunctionType.Math.Sign, new StandardFunctionView("sign"));
            RegisterFunction(FunctionType.Math.Acos, new StandardFunctionView("acos"));
            RegisterFunction(FunctionType.Math.Asin, new StandardFunctionView("asin"));
            RegisterFunction(FunctionType.Math.Atan, new StandardFunctionView("atan"));
            RegisterFunction(FunctionType.Math.Cos, new StandardFunctionView("cos"));
            RegisterFunction(FunctionType.Math.Cosh, new StandardFunctionView("cosh"));
            RegisterFunction(FunctionType.Math.Exp, new StandardFunctionView("exp"));
            RegisterFunction(FunctionType.Math.Log, new StandardFunctionView("log"));
            RegisterFunction(FunctionType.Math.Log10, new FunctionTemplateView("log(?1)"));
            RegisterFunction(FunctionType.Math.Sin, new StandardFunctionView("sin"));
            RegisterFunction(FunctionType.Math.Sinh, new StandardFunctionView("sinh"));
            RegisterFunction(FunctionType.Math.Sqrt, new StandardFunctionView("sqrt"));
            RegisterFunction(FunctionType.Math.Tan, new StandardFunctionView("tan"));
            RegisterFunction(FunctionType.Math.Tanh, new StandardFunctionView("tanh"));
            RegisterFunction(FunctionType.Math.Round, new StandardFunctionView("round"));
            //RegisterFunction(FunctionType.Math.Truncate, new StandardFunctionView("trunc"));
            RegisterFunction(FunctionType.Math.Ceiling, new StandardFunctionView("ceil"));
            RegisterFunction(FunctionType.Math.Floor, new StandardFunctionView("floor"));
            RegisterFunction(FunctionType.Math.Atan2, new StandardFunctionView("atan2"));
            RegisterFunction(FunctionType.Math.Power, new StandardFunctionView("power"));

            RegisterFunction(FunctionType.Math.Mod, new VarArgsFunctionView("", " % ", ""));
        }

        public override char OpenQuote
        {
            get { return '['; }
        }

        public override char CloseQuote
        {
            get { return ']'; }
        }

        public override bool SupportsCollectionOperation(CollectionOperator type)
        {
            return true;
        }

        public override string FormatCollectionOperation(CollectionOperator type)
        {
            if (type == CollectionOperator.Minus)
                type = CollectionOperator.Except;
            return base.FormatCollectionOperation(type);
        }

        public override void BuildSelect(NodeWriter visitor, SelectNode select)
        {
            if (select == null)
                throw new ArgumentNullException("select");
            if (select.Into != null)
                throw new NotSupportedException("sqlite not support 'insert into table2 select * from table1'");

            visitor.Builder.Append("SELECT ");

            if (select.IsDistinct)
                visitor.Builder.Append("DISTINCT ");
            if (select.Projections.Count > 0)
                visitor.VisitEnumerable(select.Projections.ToArray());
            else
                visitor.Builder.Append("*");

            visitor.Builder.AppendLine().Append("FROM ");

            var table = new TableNode(select.Name, select.Alias);
            visitor.Visit(table);

            if (select.Joins.Count() > 0)
            {
                visitor.Builder.Append(" ");
                visitor.VisitEnumerable(select.Joins.ToArray(), " ");
            }

            select.Where.Accept(visitor);
            select.Group.Accept(visitor);
            select.Having.Accept(visitor);
            select.OrderBy.Accept(visitor);

            if (select.PageSize > 0)
                visitor.Builder.AppendLine().Append("LIMIT ").Append(select.PageSize.ToString());
            if (select.PageIndex > 0)
                visitor.Builder.AppendLine().Append("OFFSET ").Append((select.PageIndex * select.PageSize + 1).ToString());
        }
    }
}
